package iet.jxufe.cn.android.productanti_counterfeitingtraceabilitysystem.database;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "Product_anti_counterfeiting_traceability.db";
    private static final int DATABASE_VERSION = 1;

    private static final String TABLE_USER_INFO = "UserInfo";
    private static final String CREATE_TABLE_USER_INFO = "CREATE TABLE " + TABLE_USER_INFO +
            " (userId INTEGER PRIMARY KEY AUTOINCREMENT, " +
            "username TEXT UNIQUE, " +
            "password TEXT, " +
            "email TEXT, " +
            "role INTEGER);";

    private static final String TABLE_PRODUCT_INFO = "ProductInfo";
    private static final String CREATE_TABLE_PRODUCT_INFO = "CREATE TABLE " + TABLE_PRODUCT_INFO +
            "(productId TEXT PRIMARY KEY, " +
            "productName TEXT NOT NULL, " +
            "qrCode BLOB NOT NULL, " +
            "category TEXT, " +
            "price DECIMAL(10, 2)," +
            "produceDate TEXT, " +
            "manufactureDate TEXT, " +
            "sellDate TEXT, " +
            "state INTEGER);";

    private static final String TABLE_TRACE_INFO = "TraceInfo";
    private static final String CREATE_TABLE_TRACE_INFO = "CREATE TABLE " + TABLE_TRACE_INFO +
            "(traceId INTEGER PRIMARY KEY AUTOINCREMENT, " +
            "productId TEXT NOT NULL, " +
            "producerId INTEGER, " +
            "producerImg BLOB, " +
            "dealerId INTEGER ," +
            "dealerImg BLOB, " +
            "consumerId INTEGER, " +
            "consumerImg BLOB);";

    public DBHelper(Context context, boolean useInMemory) {
        super(context, useInMemory ? null : DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        sqLiteDatabase.execSQL(CREATE_TABLE_USER_INFO);
        sqLiteDatabase.execSQL(CREATE_TABLE_PRODUCT_INFO);
        sqLiteDatabase.execSQL(CREATE_TABLE_TRACE_INFO);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + TABLE_USER_INFO);
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + TABLE_PRODUCT_INFO);
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + TABLE_TRACE_INFO);
        onCreate(sqLiteDatabase);
    }
}
